package buytogether.model;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.sql.DataSource;
import javax.sql.rowset.CachedRowSet;

@ManagedBean
@RequestScoped
public class SaleBean implements Serializable {
    @Resource(name="jdbc/BuyTogether")
    DataSource dataSource;
    
    private Connection getConnection() throws SQLException
    {
        if(dataSource == null)
            throw new SQLException("חיבור לבסיס הנתונים נכשל");
        Connection connection = dataSource.getConnection();
        if(connection == null)
            throw new SQLException("חיבור לבסיס הנתונים נכשל");
        return connection;
    }
    
     public ResultSet getSales() throws SQLException
    {
        Connection connection = getConnection();
        try
        {
            PreparedStatement getSales = 
                    connection.prepareStatement
                    ("SELECT CA.DESCRIPTION AS CATEGORY,\n" +
                        "CI.CITY_NAME,SC.NAME AS SHOPPING_CENTER,\n" +
                        "ST.NAME AS STORE,\n" +
                        "SA.CREATED_DATE,\n" +
                        "SA.DESCRIPTION,\n" +
                        "SA.EXPIRED_DATE  \n" +
                        "FROM APP.SALES SA,\n" +
                        "APP.STORES ST,\n" +
                        "APP.SHOPPING_CENTERS SC,\n" +
                        "APP.CITIES CI,\n" +
                        "APP.CATEGORIES CA\n" +
                        "WHERE SA.STORE = ST.ID\n" +
                        "AND ST.CATEGORY = CA.ID\n" +
                        "AND ST.SHOPPING_CENTER = SC.ID\n" +
                        "AND SC.CITY_ID = CI.CITY_ID") ;
            
            CachedRowSet rowSet = new com.sun.rowset.CachedRowSetImpl();
            rowSet.populate(getSales.executeQuery());
            return rowSet;
        }
        finally
        {
            connection.close();
        }
    }
}
